alter proc sp_ThemPhieuThuePhong
@MaPhong nvarchar(50), @NgayBD datetime,@NgayKT datetime, @TienDatCoc money, @MaNV varchar(10),@NguoiThue int
as
begin
	-- them vao bang phieu thue phong
	declare @MaPhieuThue int
	insert into PhieuThuePhong(Ma_Phong,MaNV,NgayBD, NgayKT,TienDatCoc,NguoiThue)	
						values(@MaPhong,@MaNV,@NgayBD,@NgayKT,@TienDatCoc,@NguoiThue)
	if @@ROWCOUNT > 0
	begin
		update Phong set TinhTrang='Y' where Ma_Phong = @MaPhong
		set @MaPhieuThue = (select top 1 Ma_PT from PhieuThuePhong order by Ma_PT desc)
	end
	else
	begin
		set @MaPhieuThue = 0
	end
	select @MaPhieuThue
end

alter proc sp_ThemChiTietPTPhong
@MaPhieuThue int, @MaKhachHang int,@NguoiThue int
as
begin
	insert into ChiTiet_PT(Ma_PT,Ma_KH,NguoiThue) values(@MaPhieuThue,@MaKhachHang,@NguoiThue)
end
go

alter proc sp_ThemKhachHang
@HoTen nvarchar(50), @CMND varchar(50), @DiaChi nvarchar(50), @LoaiKH varchar(50)
as
begin
	declare @MaKH int
	insert into KhachHang(HoTen,CMND,DiaChi,Loai_KH) values(@HoTen,@CMND,@DiaChi,@LoaiKH)
	if @@ROWCOUNT > 0
	begin
		set @MaKH = (select top(1) Ma_KH from KhachHang order by Ma_KH desc)
	end
	else
	begin
		set @MaKH = 0
	end
	select @MaKH
end
go

alter proc sp_ThemHoaDon
@MaPhieuThue int, @TongTien money, @KetThuc datetime
as
begin
	declare @MaHoaDon int
	declare @MaChiTietPhieuThue int
	declare @TenHD nvarchar(50) = 'hd' + convert(nvarchar(50),@MaPhieuThue)
	declare @MaPhong nvarchar(50)
	
	--insert hoa don moi
	insert into HoaDon(Ten_HD,NgayLap_HD,TongTien) values(@TenHD,@KetThuc,@TongTien)
	set @MaHoaDon = (select top(1) Ma_HD from HoaDon where NgayLap_HD=@KetThuc and Ten_HD=@TenHD and TongTien=@TongTien)
	--lay ma chi tiet phieu thue
	set @MaChiTietPhieuThue= (select top(1) b.Ma_CTPT from PhieuThuePhong a,ChiTiet_PT b where a.Ma_PT = b.Ma_PT and a.Ma_PT=@MaPhieuThue)
	set @MaPhong = (select Ma_Phong from PhieuThuePhong where Ma_PT=@MaPhieuThue)
	insert into ChiTietHD(Ma_HD,Ma_CTPT) values(@MaHoaDon,@MaChiTietPhieuThue)
	--update tinh trang phong vua thue thanh co the thue
	update Phong set TinhTrang='N' where Ma_Phong=@MaPhong
end
go

alter proc sp_DanhSachHoaDon
as
begin
	select e.TenPhong, d.NgayBD, d.NgayKT, f.Gia
	from HoaDon a,ChiTietHD b, ChiTiet_PT c, PhieuThuePhong d,Phong e, LoaiPhong f
	where a.Ma_HD=b.Ma_HD and b.Ma_CTPT=c.Ma_CTPT and c.Ma_PT = d.Ma_PT and d.Ma_Phong = e.Ma_Phong and e.Ma_LoaiPhong = f.Ma_LoaiPhong
end
go